<?php

class dbinfo{

    public $host = '127.0.0.1';
    public $port = '3306';
    public $user = 'root';
    public $pwd = 'root';
    public $charset = 'utf8';
    public $db;
    public $sql = '';
    public $dbname = '';

    public function __construct()
    {
        try{
            $this->db = new mysqli($this->host, $this->user, $this->pwd, null, $this->port);
            $sql = "set names $this->charset";
            $this->db->query($sql);
        }
        catch (Exception $e){
            return $e;
        };

    }

    public function databases()
    {
        try{
            $sql = "SELECT `SCHEMA_NAME` FROM `information_schema`.`SCHEMATA`";
            $data = [];
            if ($row = $this->db->query($sql)) {
                if ($row->num_rows > 0) {
                    while ($arr = $row->fetch_array()) {
                        $data[] = $arr['SCHEMA_NAME'];
                    }
                }
            }
        }
        catch (Exception $e){
            return $e;
        };

        return $data;

    }

    public function tables($DBName = '')
    {
        if(empty($DBName))
        {
            return "数据库名不能为空！";
        }
        try{
            $sql = "SELECT * FROM `information_schema`.`TABLES` WHERE `TABLE_SCHEMA`='$DBName'";
            $data = [];
            if ($row = $this->db->query($sql)) {
                if ($row->num_rows > 0) {
                    while ($arr = $row->fetch_array()) {
                        $data[] = $arr;
                    }
                }
            }
        }
        catch (Exception $e){
            return $e;
        };
        return $data;
    }

    public function columns($DBName = '', $tableName = '')
    {
        if(empty($DBName))
        {
            return "数据库名不能为空！";
        }
        if(empty($tableName))
        {
            return "表名不能为空！";
        }
        try{
            $sql = "SELECT * FROM `information_schema`.`COLUMNS` WHERE `TABLE_SCHEMA`='$DBName' && TABLE_NAME='$tableName'";
            $data = [];
            if ($row = $this->db->query($sql)) {
                if ($row->num_rows > 0) {
                    while ($arr = $row->fetch_array()) {
                        $data[] = $arr;
                    }
                }
            }
        }
        catch (Exception $e){
            return $e;
        };
        return $data;
    }
}
$show_databases = false;
$show_tables = false;

$DB = new dbinfo();
$dbList = $DB->databases();

?>
<!DOCTYPE html>
<html lang="zh-CN">
    <head>
        <title>数据字典查看器</title>
        <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
        <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1, user-scalable=no">
        <link href="//cdn.bootcss.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet">
        <link href="//cdn.bootcss.com/bootstrap/3.3.7/css/bootstrap-theme.min.css" rel="stylesheet">
        <style>
            *{font-family:"Microsoft YaHei";}
            .btn{ margin: 5px auto;}
            .navbar-tables{
                position: fixed;
            }
        </style>
    </head>
    <body data-spy="scroll" data-target=".navbar-tables">
        <?php
//error_reporting(0); 
        if (!empty($_GET["dbname"])) {
            $dbName = $_GET["dbname"];
            if (!in_array($dbName, $dbList)) {
                echo "数据库不存在！";
                die;
            }
            $tables = $DB->tables($dbName);

            if (!empty($tables)) {

                $data = [];
                foreach ($tables as $k => $v) {

                    $columns = $DB->columns($dbName,$v['TABLE_NAME']);

                    $data[$k]['table'] = $v["TABLE_NAME"];
                    $data[$k]['table_comment'] = $v["TABLE_COMMENT"];
                    $data[$k]['th'] = array( '名称', '类型', '非空', '主键', '默认', '备注');
                    foreach ($columns as $key => $val) {
                        $data[$k]['td'][$key] = $val;
                    }
                }
            }
            $show_tables = true;
        } else {
            $show_databases = true;
        }

        ?>
        <?php if ($show_databases): ?>
            <header class="row">
                <center><h1>数据字典浏览器</h1></center>
            </header>
            <div class="container-fluid">
                <p>
                    <?php foreach ($dbList AS $k => $v): ?>
                        <a href="?dbname=<?php echo $v; ?>" class="btn btn-default"  role="button"><?php echo $v; ?></a>
                    <?php endforeach; ?>
                </p>
            </div>
        <?php elseif ($show_tables): ?>
            <style>
                .left-menu{ background: #3a3a3a;}
                .left-menu a{color: #c3c3c3;}
                .left-menu .nav>li>a:focus,.left-menu .nav>li>a:hover {
                    color:  #337ab7;
                }
                body {
                    position: relative;
                }
                .navbar-tables{right:0;}
                .navbar-tables .nav>li>a {
                    padding: 2px 15px;
                }
            </style>
            <div class="row">
                <div class="col-lg-2 left-menu">
                    <ul  class="nav nav-pills nav-stacked" role="tablist">				
                        <?php foreach ($dbList AS $k => $v): ?>
                            <li role="presentation" <?php if ($v == $dbName): ?> class="active"<?php endif; ?>><a href="?dbname=<?php echo $v; ?>"><?php echo $v; ?></a></li>
                        <?php endforeach; ?>
                    </ul>
                </div>
                <div class="col-lg-10">
                    <div class="row col-lg-9" id="header">
                        <div class="row">
                            <h1>当前数据库：<?php echo $dbName; ?></h1>
                        </div>
                        <hr/>
                        <div class="row">
                            <div>
                                <?php foreach ($data AS $k => $v): ?>
                                    <h2 id="<?php echo $v["table"]; ?>">表名：<?php echo $v["table"]; ?> <?php echo $v["table_comment"]; ?></h2>
                                    <table class="table table-bordered table-striped table-hover">
                                        <tr>
                                            <?php foreach ($v["th"] AS $th): ?>
                                                <th><?php echo $th; ?></th>
                                            <?php endforeach; ?>
                                        </tr>
                                        <?php foreach ($v["td"] AS $td): ?>
                                            <tr>
                                                <td><?php echo $td['COLUMN_NAME']; ?></td>
                                                <td><?php echo $td['COLUMN_TYPE']; ?></td>
                                                <td><?php echo $td['IS_NULLABLE']; ?></td>
                                                <td><?php echo $td['COLUMN_KEY']; ?></td>
                                                <td><?php echo $td['COLUMN_DEFAULT']; ?></td>
                                                <td><?php echo $td['COLUMN_COMMENT']; ?></td>
                                            </tr>
                                        <?php endforeach; ?>
                                    </table>
                                <?php endforeach; ?>
                            </div>
                        </div>
                    </div>
                    <div class="col-lg-2 navbar-tables" data-spy="affix">
                        <ul class="nav nav-pills nav-stacked" role="tablist">
                            <?php foreach ($data AS $k => $v): ?>
                                <li><a href="#<?php echo $v['table']; ?>"><?php echo !empty($v["table_comment"]) ? "[".$v["table_comment"]."]" : ""; ?> <?php echo $v["table"]; ?></a></li>
                            <?php endforeach; ?>
                            <li><a href="#header">返回顶部</a></li>
                        </ul>
                    </div>
                </div>
            </div>
            <footer class="row footer">
            </footer>
        <?php endif; ?>
    </body>
    <script src="//cdn.bootcss.com/jquery/3.1.0/jquery.min.js"></script>
    <script src="//cdn.bootcss.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
    <script src="//cdn.bootcss.com/bootstrap/3.2.0/js/scrollspy.min.js"></script>
    <script src="//cdn.bootcss.com/bootstrap/3.2.0/js/affix.js"></script>
    <script>
        $('body').on('activate.bs.scrollspy', function () {
            if($(".affix .active").offset()){
                console.log("active",$(".affix .active").offset().top)
                console.log("active",$(window).scrollTop())
                console.log("active",$(".affix .active").offset().top-$(window).scrollTop())
                // if($(".affix .active").offset().top- $(window).scrollTop() > 100){
                //     $(".affix").css("margin-top",-($(".affix .active").offset().top-$(window).scrollTop()))
                // }else {
                //     $(".affix").css("margin-top",0)
                // }
            }
        })
        $('.navbar-tables').affix();
    </script>
</html>
